import pandas as pd
import numpy as np
import os
import datetime
from icalendar import Calendar, Event, Alarm
from openpyxl import Workbook
from openpyxl import load_workbook  # 导入模块
from os.path import basename
from os.path import dirname

class ical(object):
    # 定义新的日历文件
    def __init__(self, icsname):
        self.cal = Calendar()
        self.cal.add('VERSION', '2.0')
        self.cal.add('X-WR-CALNAME', icsname)
        self.cal.add('X-APPLE-CALENDAR-COLOR', '#540EB9')
        self.cal.add('X-WR-TIMEZONE', 'Asia/Shanghai')
        
    # 向日历文件中添加事件    
    def add_event(self,DTSTART, DTEND, SUMMARY, DESCRIPTION, LOCATION):
        event = Event()
        event.add('UID', '2000')
        event.add('DTEND;VALUE=DATE', DTEND)
        event.add('DTSTART;VALUE=DATE', DTSTART)
        event.add('SUMMARY', SUMMARY)
        event.add('SEQUENCE', '0')
        event.add('DESCRIPTION', DESCRIPTION)
        event.add('LOCATION', LOCATION)
        alarm = Alarm()
        alarm.add('ACTION', 'NONE')
        alarm.add('TRIGGER;VALUE=DATE-TIME', '19760401T005545Z')
        event.add_component(alarm)
        self.cal.add_component(event)
    # 将日历作为ics文件输出
    def write_ical(self,filepath):
        f= open(filepath,'wb')
        f.write(self.cal.to_ical())
        # print(self.cal.to_ical())
        f.close()
        

class ExcelOp(object):
    def __init__(self, file):
        self.file = file
        self.wb = load_workbook(self.file)
        sheets = self.wb.get_sheet_names()
        self.sheet = sheets[0]
        self.ws = self.wb[self.sheet]

    # 获取表格的总行数和总列数
    def get_row_clo_num(self):
        rows = self.ws.max_row
        columns = self.ws.max_column
        return rows, columns

    # 获取某个单元格的值
    def get_cell_value(self, row, column):
        cell_value = self.ws.cell(row=row, column=column).value
        return cell_value

    # 获取某列的所有值
    def get_col_value(self, column):
        rows = self.ws.max_row
        column_data = []
        for i in range(1, rows + 1):
            cell_value = self.ws.cell(row=i, column=column).value
            column_data.append(cell_value)
        return column_data

    # 获取某行所有值
    def get_row_value(self, row):
        columns = self.ws.max_column
        row_data = []
        for i in range(1, columns + 1):
            cell_value = self.ws.cell(row=row, column=i).value
            row_data.append(cell_value)
        return row_data

    # 设置某个单元格的值
    def set_cell_value(self, row, colunm, cellvalue):
        try:
            self.ws.cell(row=row, column=colunm).value = cellvalue
            self.wb.save(self.file)
        except:
            self.ws.cell(row=row, column=colunm).value = "writefail"
            self.wb.save(self.file)


filepath = input('请输入计划电子表格全路径：')
ex = ExcelOp(filepath)
j = ex.get_row_value(5)
mycal = ical('tjx_测试文件')

icount = ex.get_row_clo_num()
for i in range(2,icount[0]+1):
    temp = ex.get_row_value(i)
    DTSTART = temp[4].strftime('%Y%m%d')+'T'+temp[5].strftime('%H%M%S')
    DTEND = temp[6].strftime('%Y%m%d')+'T'+temp[7].strftime('%H%M%S')
    SUMMARY = temp[1]
    DESCRIPTION = temp[2]
    LOCATION = temp[3]
    mycal.add_event(DTSTART, DTEND, SUMMARY, DESCRIPTION, LOCATION)
savedir= dirname(filepath)
savename = basename(filepath).split('.')[0]+'.ics'
savepath = os.path.join(savedir, savename)
mycal.write_ical(savepath)
print('操作结束')
